package com.ecjtu.stadiumre_servations.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ecjtu.stadiumre_servations.entity.domain.DataAnalysis;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface DataAnalysisMapper extends BaseMapper<DataAnalysis> {

//statsAllByPeriodRough
    //-------------------------------------COUNT BY DAY
    //totalCount
    @Select("SELECT SUM(data_analysis.total_count) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}" )
    Integer totalCount(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalSingleCount
    @Select("SELECT SUM(data_analysis.single_count) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Integer totalSingleCount(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalGroupCount
    @Select("SELECT SUM(data_analysis.group_count) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Integer totalGroupCount(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);

    //-------------------------------------COUNT BY DAY ID
    //totalCount
    @Select("SELECT SUM(data_analysis.total_count) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}" )
    Integer totalCountById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalSingleCount
    @Select("SELECT SUM(data_analysis.single_count) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Integer totalSingleCountById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalGroupCount
    @Select("SELECT SUM(data_analysis.group_count) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Integer totalGroupCountById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);

    //-------------------------------------TIME BY DAY
    //totalTime
    @Select("SELECT SUM(data_analysis.total_time) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Long totalTime(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalSingleTime
    @Select("SELECT SUM(data_analysis.single_time) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Long totalSingleTime(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalGroupTime
    @Select("SELECT SUM(data_analysis.group_time) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Long totalGroupTime(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //-------------------------------------TIME BY DAY ID
    //totalCount
    @Select("SELECT SUM(data_analysis.total_time) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}" )
    Long totalTimeById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalSingleCount
    @Select("SELECT SUM(data_analysis.single_time) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Long totalSingleTimeById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalGroupCount
    @Select("SELECT SUM(data_analysis.group_time) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Long totalGroupTimeById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);

    //-------------------------------------PROFIT BY DAY
    //totalProfit
    @Select("SELECT SUM(data_analysis.total_profit) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Double totalProfit(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalSingleProfit
    @Select("SELECT SUM(data_analysis.single_profit) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Double totalSingleProfit(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //totalGroupProfit
    @Select("SELECT SUM(data_analysis.group_profit) FROM data_analysis WHERE stat_time BETWEEN #{startDay} AND #{endDay}")
    Double totalGroupProfit(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay);
    //-------------------------------------PROFIT BY DAY ID
    //totalCount
    @Select("SELECT SUM(data_analysis.total_profit) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}" )
    Double totalProfitById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalSingleCount
    @Select("SELECT SUM(data_analysis.single_profit) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Double totalSingleProfitById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);
    //totalGroupCount
    @Select("SELECT SUM(data_analysis.group_profit) FROM data_analysis WHERE venue_id = #{venueId} AND stat_time BETWEEN #{startDay} AND #{endDay}")
    Double totalGroupProfitById(@Param("startDay")LocalDate startDay, @Param("endDay")LocalDate endDay, @Param("venueId")String venueId);



    @Insert("INSERT INTO data_wait_for_update(booking_id) values(#{bookingId})")
    void saveForUpdate (@Param("bookingId")String bookingId);

    @Select("SELECT booking_id FROM data_wait_for_update WHERE booking_id = #{bookingId}")
    String selectForUpdate(@Param("bookingId")String bookingId);

    @Select("SELECT booking_id FROM booking WHERE( (booking_status = #{status}) AND " +
            "( (booking_start_time BETWEEN #{start} AND #{end}) OR (booking_end_time BETWEEN #{start} AND #{end}) ) ) ")
    List<String> booking_status(@Param("status")String status, @Param("start")LocalDateTime start, @Param("end")LocalDateTime end);

    @Delete("DELETE FROM data_wait_for_update WHERE booking_id = #{bookingId}")
    void deleteForUpdate (@Param("bookingId")String booking_id);


}
